package com.motu.vertx.module.utility.clickhouse;

import com.motu.vertx.module.utility.toolset.Tool;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.Properties;

public class ClickhouseTool {
    private static final Logger logger = LoggerFactory.getLogger(ClickhouseTool.class);

    public static int queryTimeout;

    public static void init(JsonObject appConfig) {
        JsonObject jsonConfig = appConfig.getJsonObject("housepower-clickhouse");
        if (jsonConfig.containsKey("queryTimeout")) {
            queryTimeout = jsonConfig.getInteger("queryTimeout");
        }else {
            queryTimeout = 3 * 60 ;//默认3分钟
        }
    }

    /***
     * 初始化连接
     * @param appConfig
     * @return
     */
    public static Connection initConnection(JsonObject appConfig) {
        JsonObject jsonConfig = appConfig.getJsonObject("housepower-clickhouse");
        try {
            Class.forName("com.github.housepower.jdbc.ClickHouseDriver");
            Properties properties = new Properties();
            properties.setProperty("user", jsonConfig.getString("user"));
            properties.setProperty("password", jsonConfig.getString("password"));
//            properties.put("socket_timeout", queryTimeout);
            if (jsonConfig.containsKey("connect_timeout")){
                properties.setProperty("connect_timeout", String.valueOf(jsonConfig.getInteger("connect_timeout")));
            }
            //TODO:query_timeout 设置超时目前没效果 先注释
//            if (jsonConfig.containsKey("query_timeout")){
//                properties.setProperty("query_timeout", String.valueOf(jsonConfig.getInteger("query_timeout")));
//            }
            Connection connection = DriverManager.getConnection(jsonConfig.getString("url"), properties);
            //能打印connection说明连接正常
            if (connection != null) {
//                logger.info("ClickhouseTool init connection is ok!");
                return connection;
            }
        } catch (ClassNotFoundException e) {
            logger.error("ClickhouseTool initConnection {}", e.getMessage());
        } catch (SQLException e) {
            logger.error("ClickhouseTool initConnection {}", e.getMessage());
        }
        logger.error("ClickhouseTool init connection error jsonConfig:{}!", jsonConfig.toString());
        return null;
    }

    /***
     * 从数据库中查询
     * @param connection
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static JsonArray queryJsonArrayFromDb(Connection connection, String sql, JsonArray params) throws SQLException {
        JsonArray array = new JsonArray();
        PreparedStatement preparedStatement = null;
        try {

            if (!params.isEmpty()) {
                sql = Tool.getSqlString(sql, params);
            }
            preparedStatement = connection.prepareStatement(sql);
//            preparedStatement.setQueryTimeout(queryTimeout);//设置超时
            ResultSet resultSet = preparedStatement.executeQuery();
            array = getRowsArray(resultSet);
        } catch (Exception e) {
            logger.error("ClickhouseTool queryJsonArrayFromDb error!!! error:{}!", Tool.getException(e));

            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
//            if(connection != null){
//                connection.close();
//            }
        }
        return array;
    }

    /**
     * 将mysql查询出来的结果集转换成JsonArray
     *
     * @param rs
     * @return
     */
    public static JsonArray getRowsArray(ResultSet rs) throws SQLException {
        JsonArray array = new JsonArray();

        // 获取列数


        // 遍历ResultSet中的每条数据
        while (rs.next()) {
            JsonObject jsonObj = new JsonObject();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 遍历每一列
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnLabel(i);
                String className = metaData.getColumnClassName(i);
//				System.out.println("columnName = "+columnName+" className = "+className);
                if (className.equalsIgnoreCase("java.lang.Integer")) {
                    jsonObj.put(columnName, rs.getInt(columnName));
                } else if (className.equalsIgnoreCase("java.lang.Long")) {
                    jsonObj.put(columnName, rs.getLong(columnName));
                } else if (className.equalsIgnoreCase("java.lang.String")) {
                    jsonObj.put(columnName, rs.getString(columnName));
                } else {
                    jsonObj.put(columnName, rs.getString(columnName));
                }
            }
            array.add(jsonObj);
        }


        return array;
    }

    /***
     * 批量入库
     * @param connection
     * @param sql
     * @param paramsList
     * @param dateTimeIndex 指定第一个为时间字段，显式转成datetime入库，索引从0开始，如果没有时间字段填-1
     * @return
     * @throws SQLException
     */
    public static int batchToDb(Connection connection, String sql, JsonArray paramsList, int dateTimeIndex) throws SQLException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
//            logger.warn("ClickhouseTool batchToDb sql:{} ", sql);
            //判断字段的数据类型
            for (int i = 0; i < paramsList.size(); i++) {
                JsonArray params = paramsList.getJsonArray(i);
                for (int j = 0; j < params.size(); j++) {
                    Object obj = params.getValue(j);
                    if (dateTimeIndex == j) {
                        //显式转换成sql时间对象再插入
                        java.sql.Timestamp time = Timestamp.valueOf((String) obj);
                        preparedStatement.setTimestamp(j + 1, (java.sql.Timestamp) time);
                    } else {
                        if (obj instanceof Integer) {
                            preparedStatement.setInt(j + 1, (Integer) obj);
                        } else if (obj instanceof Long) {
                            preparedStatement.setLong(j + 1, (Long) obj);
                        } else if (obj instanceof String) {
                            preparedStatement.setString(j + 1, (String) obj);
                        } else {
                            String type = obj.getClass().getName();
                            logger.warn("ClickhouseTool batchToDb unknown obj type = " + type);
                        }
                    }
                }
                preparedStatement.addBatch();
            }
            int ret[] = preparedStatement.executeBatch();
//           int ret =  preparedStatement.executeUpdate();
            if (ret.length != paramsList.size()) {
                logger.error("ClickhouseTool batchToDb ret.length:{} paramsList.size:{}", ret.length, paramsList.size());
            }
            return ret.length;
//            logger.warn("ClickhouseTool batchToDb ret.length = " + ret.length);
//            for(int a:ret){
//                logger.warn("ClickhouseTool batchToDb a = " + a);
//                //打印出来 ClickhouseTool batchToDb a = 1 ，可能是没有自增id，如有的话可能返回自增id
//            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("ClickhouseTool batchToDb {}", e.getMessage());
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return 0;
    }
}
